const express = require('express');
const router = express.Router();
const mysql = require('mysql2/promise');
const dbConfig = require('../config/database');

// 获取所有用户（带分页和排序）
router.get('/', async (req, res) => {
    try {
        const page = parseInt(req.query.page) || 1;
        const limit = parseInt(req.query.limit) || 8;
        const offset = (page - 1) * limit;
        const sortColumn = req.query.sortColumn || 'created_at';
        const sortDirection = req.query.sortDirection || 'desc';

        const connection = await mysql.createConnection(dbConfig);

        const [countResult] = await connection.execute('SELECT COUNT(*) as total FROM users');
        const total = countResult[0].total;

        const [rows] = await connection.execute(
            `SELECT * FROM users ORDER BY ${sortColumn} ${sortDirection} LIMIT ${limit} OFFSET ${offset}`
        );

        await connection.end();

        res.json({
            data: rows,
            pagination: {
                page,
                limit,
                total,
                totalPages: Math.ceil(total / limit)
            }
        });
    } catch (error) {
        console.error('获取用户列表失败:', error);
        res.status(500).json({ error: '获取用户列表失败' });
    }
});

// 导出用户数据
router.get('/export', async (req, res) => {
    try {
        const connection = await mysql.createConnection(dbConfig);
        const [rows] = await connection.execute('SELECT * FROM users');
        await connection.end();

        // 生成 CSV 数据
        const csvHeader = 'ID,姓名,邮箱,电话,注册时间\n';
        const csvData = rows.map(user =>
            `${user.id},"${user.name}","${user.email}","${user.phone || ''}","${user.created_at}"`
        ).join('\n');

        res.setHeader('Content-Type', 'text/csv; charset=utf-8');
        res.setHeader('Content-Disposition', 'attachment; filename=users.csv');
        res.send(csvHeader + csvData);
    } catch (error) {
        res.status(500).json({ error: '导出数据失败' });
    }
});

// 搜索用户
router.get('/search', async (req, res) => {
    try {
        const { q } = req.query;
        const connection = await mysql.createConnection(dbConfig);
        const [rows] = await connection.execute(
            'SELECT * FROM users WHERE name LIKE ? OR email LIKE ? OR phone LIKE ?',
            [`%${q}%`, `%${q}%`, `%${q}%`]
        );
        await connection.end();
        res.json(rows);
    } catch (error) {
        res.status(500).json({ error: '搜索用户失败' });
    }
});

// 获取单个用户
router.get('/:id', async (req, res) => {
    try {
        const connection = await mysql.createConnection(dbConfig);
        const [rows] = await connection.execute('SELECT * FROM users WHERE id = ?', [req.params.id]);
        await connection.end();

        if (rows.length === 0) {
            return res.status(404).json({ error: '用户不存在' });
        }
        res.json(rows[0]);
    } catch (error) {
        res.status(500).json({ error: '获取用户信息失败' });
    }
});

// 添加用户
router.post('/', async (req, res) => {
    try {
        const { name, email, phone, password } = req.body;

        // 添加输入验证
        if (!name || !email) {
            return res.status(400).json({ error: '姓名和邮箱为必填项' });
        }

        const connection = await mysql.createConnection(dbConfig);

        // 检查邮箱是否已存在
        const [existing] = await connection.execute(
            'SELECT id FROM users WHERE email = ?',
            [email]
        );

        if (existing.length > 0) {
            await connection.end();
            return res.status(400).json({ error: '该邮箱已被注册' });
        }

        // 插入新用户，设置默认密码为 123456
        const defaultPassword = '123456';
        const [result] = await connection.execute(
            'INSERT INTO users (name, email, phone, password) VALUES (?, ?, ?, ?)',
            [name, email, phone || '', defaultPassword]
        );

        await connection.end();

        // 返回新添加的用户信息（不包含密码）
        res.status(201).json({
            id: result.insertId,
            name,
            email,
            phone: phone || ''
        });
    } catch (error) {
        console.error('添加用户失败:', error);
        res.status(500).json({
            error: '添加用户失败',
            details: error.message
        });
    }
});

// 更新用户
router.put('/:id', async (req, res) => {
    try {
        const { name, email, phone, password } = req.body;
        const connection = await mysql.createConnection(dbConfig);

        // 检查邮箱是否被其他用户使用
        const [existing] = await connection.execute(
            'SELECT id FROM users WHERE email = ? AND id != ?',
            [email, req.params.id]
        );
        if (existing.length > 0) {
            await connection.end();
            return res.status(400).json({ error: '该邮箱已被其他用户使用' });
        }

        // 构建更新语句
        let sql = 'UPDATE users SET name = ?, email = ?, phone = ?';
        let params = [name, email, phone || ''];

        // 如果提供了新密码，则更新密码
        if (password) {
            sql += ', password = ?';
            params.push(password);
        }

        // 添加 WHERE 子句
        sql += ' WHERE id = ?';
        params.push(req.params.id);

        const [result] = await connection.execute(sql, params);

        await connection.end();

        if (result.affectedRows === 0) {
            return res.status(404).json({ error: '用户不存在' });
        }

        // 返回更新后的用户信息（不包含密码）
        res.json({
            id: parseInt(req.params.id),
            name,
            email,
            phone: phone || ''
        });
    } catch (error) {
        console.error('更新用户失败:', error);
        res.status(500).json({
            error: '更新用户失败',
            details: error.message
        });
    }
});

// 删除用户
router.delete('/:id', async (req, res) => {
    try {
        const connection = await mysql.createConnection(dbConfig);

        // 检查用户是否有未归还的图书
        const [borrowings] = await connection.execute(
            'SELECT id FROM borrowings WHERE user_id = ? AND status = "borrowed"',
            [req.params.id]
        );

        if (borrowings.length > 0) {
            await connection.end();
            return res.status(400).json({ error: '该用户还有未归还的图书，无法删除' });
        }

        const [result] = await connection.execute('DELETE FROM users WHERE id = ?', [req.params.id]);
        await connection.end();

        if (result.affectedRows === 0) {
            return res.status(404).json({ error: '用户不存在' });
        }
        res.status(204).end();
    } catch (error) {
        res.status(500).json({ error: '删除用户失败' });
    }
});

module.exports = router; 